#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

dws_trade_user_payment_1d="
insert overwrite table ${APP}.dws_trade_user_payment_1d partition (dt = '$do_date')
select user_id,
       sum(if(payment_status is not null, 1, 0)),
       sum(if(payment_status is not null, total_amount, 0))
from ${APP}.dwd_trade_order_info_inc
where dt = '$do_date'
group by user_id;
"
dws_course_user_comment_1d="
insert overwrite table ${APP}.dws_course_user_comment_1d partition (dt = '$do_date')
select course_id,
       dim.course_name,
       user_id,
       review_stars,
       if(review_stars > 3, true, false),
       if(review_stars = 5, true, false)
from ${APP}.dwd_course_review_info_inc dwd
         join ${APP}.dim_course_info_full dim on dwd.course_id = dim.id
where dwd.dt = '$do_date';
"
dws_trade_user_course_order_1d="
insert overwrite table ${APP}.dws_trade_user_course_order_1d partition(dt='$do_date')
select
    toi.user_id,
    -- copy 0000-00-00
    if(datediff(substr(current_d,0,10), CONCAT(substr(current_d,0,4),substr(birthday,4,10)) )>=0,
        (substr(current_d,0,4) - substr(birthday,0,4)),
        (substr(current_d,0,4) - substr(birthday,0,4) -1)) as age,
    toi.course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name,
    order_count_1d,
    order_amount_1d
from (
    select
        user_id,
        course_id,
        count(final_amount) as order_count_1d,
        sum(final_amount) as order_amount_1d,
        min(concat(dt)) as current_d
    from ${APP}.dwd_trade_order_info_inc
    where dt='$do_date' and final_amount is not null
    group by user_id,course_id
) as toi left join (
    select
        distinct(id) as user_id,
        birthday
    from ${APP}.dim_user_zip
) as uz on toi.user_id=uz.user_id
left join (
    select
        distinct(id) as course_id,
        subject_id,
        course_name,
        subject_name,
        category_id,
        category_name
    from ${APP}.dim_course_info_full
) as ci on toi.course_id=ci.course_id;
"

dws_trade_province_order_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt='$do_date')
select
    province_id,
    name,
    area_code,
    iso_code,
    iso_3166_2,
    order_user_1d,
    order_count_1d,
    order_total_amount_1d
from
(select province_id,
        count(distinct(user_id)) order_user_1d,
        count(distinct (order_id)) order_count_1d,
        sum(total_amount)          order_total_amount_1d,
        dt
 from ${APP}.dwd_trade_order_info_inc
 group by province_id, dt
)o
left join
(
    select
        id,
        name,
        area_code,
        iso_code,
        iso_3166_2
    from ${APP}.dim_province_full
    where dt='$do_date'
)p
on o.province_id=p.id;
"

dws_trade_user_cart_add_1d="
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt='$do_date')
select
    user_id,
    count(*)
from ${APP}.dwd_trade_cart_add_inc
where dt='$do_date'
group by user_id;
"
dws_traffic_session_page_view_1d="
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition (dt = '$do_date')
select session_id,
       source_site,
       mid_id,
       brand,
       model,
       operate_system,
       version_code,
       channel,
       sum(during_time) during_time_1d,
       count(*)         page_count_1d
from ${APP}.dwd_traffic_page_view_inc
where dt = '$do_date'
group by session_id, source_site, mid_id, brand, model, operate_system, version_code, channel;
"
dws_traffic_page_visitor_page_view_1d="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_1d partition (dt = '$do_date')
select mid_id,
       brand,
       model,
       operate_system,
       page_id,
       sum(during_time) during_time_1d,
       count(*)         view_count_1d
from ${APP}.dwd_traffic_page_view_inc
where dt = '$do_date'
group by mid_id, brand, model, operate_system, page_id;
"

dws_action_video_watch_1d="
insert overwrite table ${APP}.dws_action_video_watch_1d partition(dt='$do_date')
select
 h.video_id,i.video_name,h.chapter_id,h.chapter_name,h.course_name,h.course_id,h.watch_user_count_1d,h.play_count_1d,h.play_avg_time_1d
from
(select f.video_id,f.chapter_id,g.chapter_name,f.course_name,f.course_id,f.watch_user_count_1d,f.play_count_1d,f.play_avg_time_1d
from
(select d.video_id,d.chapter_id,e.course_name,d.course_id,d.watch_user_count_1d,d.play_count_1d,d.play_avg_time_1d
from
(select c.video_id,
       c.chapter_id,
        c.course_id,
       c.play_count_1d,
       c.watch_user_count_1d,
       c.play_avg_time_1d
from
(select
        a.chapter_id,
       a.video_id,
        a.course_id,
        a.play_count_1d,
        a.watch_user_count_1d,
       b.play_avg_time_1d
from
(select chapter_id,
       video_id,
        course_id,
       count(user_id) play_count_1d,
       count(distinct user_id) watch_user_count_1d
from ${APP}.dwd_action_chapter_process_inc
where dt='$do_date'
group by video_id,chapter_id,course_id) a
join
(select chapter_id,
       avg(position_sec) play_avg_time_1d
from ${APP}.dwd_action_chapter_process_inc
where dt='$do_date'
group by chapter_id) b
on a.chapter_id = b.chapter_id) c ) d

join
(select id,course_name
from
${APP}.dim_course_info_full) e
on d.course_id = e.id) f
join
(select id,chapter_name
from ${APP}.ods_chapter_info_full) g
on f.chapter_id = g.id) h
join (select data.id,data.video_name,dt from ${APP}.ods_video_info_inc where dt = '$do_date') i
on h.video_id = i.id;
"

dws_action_chapter_end_1d="
insert overwrite table ${APP}.dws_action_chapter_end_1d partition(dt='$do_date')

select f.chapter_id,g.chapter_name,f.course_id,f.course_name,f.watch_end_user_count_1d,watch_end_count_1d
from
(select d.chapter_id,d.course_id,e.course_name,d.watch_end_user_count_1d,d.watch_end_count_1d
from
(select c.video_id,c.chapter_id,c.course_id,count(b.user_id) watch_end_count_1d,count(distinct b.user_id)  watch_end_user_count_1d
from ${APP}.dwd_action_chapter_process_inc c join
(select user_id,
       video_id,
       sum(int(play_sec)) sumsec

from (select
    common.uid user_id,
    appvideo.play_sec,
    appvideo.video_id

from ${APP}.ods_log_inc
where dt = '$do_date') a
where play_sec is not null
group by video_id,user_id) b
where c.dt = '$do_date' and c.position_sec >= c.during_sec*0.9
                            and b.sumsec >= c.during_sec*0.9
group by c.video_id,c.chapter_id,c.course_id) d
join
(select id,course_name
from
${APP}.dim_course_info_full) e
on d.course_id = e.id) f
join
(select id,chapter_name
from ${APP}.ods_chapter_info_full) g
on f.chapter_id = g.id;
"

dws_action_course_end_1d="
insert overwrite table ${APP}.dws_action_course_end_1d partition(dt='$do_date')
select d.course_id,e.course_name,d.course_end_user_count_1d,d.course_end_count_1d

from
(select c.course_id,count(c.user_id) course_end_user_count_1d ,  count(distinct c.user_id) course_end_count_1d
from ${APP}.dwd_action_chapter_process_inc c
join (select user_id,
       video_id,
       sum(int(play_sec)) sumsec

from (select
    common.uid user_id,
    appvideo.play_sec,
    appvideo.video_id

from ${APP}.ods_log_inc
where dt = '$do_date') a
where play_sec is not null
group by video_id,user_id) b
on c.user_id = b.user_id
where  c.dt = '$do_date' and c.position_sec >= c.during_sec*0.9
                            and b.sumsec >= c.during_sec*0.9
group by c.course_id) d

join
(select id,course_name
from
${APP}.dim_course_info_full) e
on d.course_id = e.id;
"

dws_sourse_order_1d="
insert overwrite table ${APP}.dws_sourse_order_1d partition (dt = '$do_date')
select d1.source_site,
       count(d2.total_amount),
       count(distinct d2.user_id),
       count(distinct d1.user_id)
from ${APP}.dwd_traffic_page_view_inc d1
         full join ${APP}.dwd_trade_order_info_inc d2 on d1.user_id = d2.user_id
where d1.dt = '$do_date'
group by d1.source_site;
"

dws_action_question_1d="
insert overwrite table ${APP}.dws_action_question_1d partition(dt='$do_date')
select
    question_id,
    count(question_id) as question_count_1d,
    sum(if(is_correct='1', 1, 0)) as is_correct_count_1d
from ${APP}.dwd_action_question_inc
WHERE dt='$do_date' and is_correct IS NOT NULL
GROUP BY question_id;
"

dws_action_paper_1d="
insert overwrite table ${APP}.dws_action_paper_1d partition(dt='$do_date')
select
    paper_id,
    course_id,
    user_id,
    sum(score) as score_sum_1d,
    sum(duration_sec) as duration_sum_1d,
    count(user_id) as times_count_1d
from ${APP}.dwd_action_test_inc
WHERE dt='$do_date' and duration_sec IS NOT NULL
GROUP BY paper_id,course_id,user_id;
"

case $1 in
    "dws_trade_user_payment_1d" )
        hive -e "$dws_trade_user_payment_1d"
    ;;
    "dws_course_user_comment_1d" )
        hive -e "$dws_course_user_comment_1d"
    ;; 
    "dws_trade_user_course_order_1d" )
        hive -e "$dws_trade_user_course_order_1d"
    ;; 
    "dws_trade_province_order_1d" )
        hive -e "$dws_trade_province_order_1d"
    ;; 
     "dws_trade_user_cart_add_1d" )
        hive -e "$dws_trade_user_cart_add_1d"
    ;; 
    "dws_traffic_session_page_view_1d" )
        hive -e "$dws_traffic_session_page_view_1d"
    ;; 
    "dws_traffic_page_visitor_page_view_1d" )
        hive -e "$dws_traffic_page_visitor_page_view_1d"
    ;; 
     "dws_action_video_watch_1d" )
        hive -e "$dws_action_video_watch_1d"
    ;; 
     "dws_action_chapter_end_1d" )
        hive -e "$dws_action_chapter_end_1d"
    ;; 
    "dws_action_course_end_1d" )
        hive -e "$dws_action_course_end_1d"
    ;; 
    "dws_sourse_order_1d" )
        hive -e "$dws_sourse_order_1d"
    ;; 
    "dws_action_question_1d" )
        hive -e "$dws_action_question_1d"
    ;; 
    "dws_action_paper_1d" )
        hive -e "$dws_action_paper_1d"
    ;; 

    "all" )
        hive -e "$dws_trade_user_payment_1d$dws_course_user_comment_1d$dws_trade_user_course_order_1d$dws_trade_province_order_1d$dws_trade_user_cart_add_1d$dws_traffic_session_page_view_1d$dws_traffic_page_visitor_page_view_1d$dws_action_video_watch_1d$dws_action_chapter_end_1d$dws_action_course_end_1d$dws_sourse_order_1d$dws_action_question_1d$dws_action_paper_1d"
    ;;
esac
